﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Configuration;
using System.Configuration;
using System.IO;
using System.Data.OleDb;
using System.Data;

namespace InformationSystem.Utility
{
    public class ExcelUtility
    {
        public static TableScheme ReadExcelScheme(string filePath)
        {
            //read config  to get connection string
            ConnectionStringSettings setting = WebConfigurationManager.ConnectionStrings["Excel2003"];
            if (setting == null) return null;
            string connstring = setting.ProviderName + setting.ConnectionString;
            connstring = string.Format(connstring, filePath);
            if (!File.Exists(filePath))
                return null;

            OleDbConnection conn = new OleDbConnection(connstring);
            // string selectext = "";
            //调用Excel的读取方法，读取EXCEL的中所有的表
            DataTable dt = null;
            try
            {
                conn.Open();
                dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            }
            catch (Exception)
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                return null;
            }
            //tableList.Items.Add(item["TABLE_NAME"]);
            // 智能选取列最多的表格作为源

            //调用Excel的读取方法，读取EXCEL的表头
            //conn.Open();
            DataTable dtColumn = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, null, null });
            conn.Close();


            DataRow[] maxdr = null;
            string tableName = string.Empty;
            foreach (DataRow item in dt.Rows)
            {
                DataRow[] drs = dtColumn.Select(string.Format("TABLE_NAME='{0}'", item["TABLE_NAME"]));
                if (maxdr == null)
                {
                    maxdr = drs;
                    tableName = item["TABLE_NAME"].ToString();
                }
                else
                {
                    if (drs.Length > maxdr.Length)
                    {
                        maxdr = drs;
                        tableName = item["TABLE_NAME"].ToString();
                    }
                }
            }
            if (maxdr == null) return null;
            TableScheme ts = new TableScheme();
            ts.TableName = tableName;
            foreach (DataRow item in maxdr)
            {
                ts.FieldList.Add(new ExcelFieldInfo(item["COLUMN_NAME"].ToString(), (OleDbType)item["DATA_TYPE"]));
            }
            return ts;
        }

        public static DataTable ReadExcel(string filePath, string tableName)
        {
            ConnectionStringSettings setting = WebConfigurationManager.ConnectionStrings["Excel2003"];
            if (setting == null) return null;
            string connstring = setting.ProviderName + setting.ConnectionString;
            connstring = string.Format(connstring, filePath);
            if (!File.Exists(filePath))
                return null;
            string selectSql = "select * from [{0}]";
            OleDbConnection conn = new OleDbConnection(connstring);
            OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format(selectSql, tableName), conn);
            // string selectext = "";
            //调用Excel的读取方法，读取EXCEL的数据
            DataTable dt = new DataTable();
            try
            {
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                return null;
            }

        }
    }
}
